package com.gitee.melin.bee.core.jdbc.dialect.impl;

import com.gitee.melin.bee.core.jdbc.dialect.AbstractJdbcDialect;
import com.gitee.melin.bee.core.jdbc.enums.DataSourceType;
import com.gitee.melin.bee.core.jdbc.relational.BeeTableType;
import com.gitee.melin.bee.core.jdbc.relational.ConnectionInfo;
import java.sql.*;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;

public class SqlServerJdbcDialect extends AbstractJdbcDialect {

    @Override
    public DataSourceType getDataSourceType() {
        return DataSourceType.SQLSERVER;
    }

    private static final String[] SYS_DBS = new String[] {"GUEST", "CDC", "GUEST", "INFORMATION_SCHEMA", "SYS"};

    public SqlServerJdbcDialect(ConnectionInfo connectionInfo) {
        super(connectionInfo);
    }

    public SqlServerJdbcDialect(Connection connection) {
        super(connection);
    }

    @Override
    protected Driver getDriver() throws SQLException {
        return loadDriver(DataSourceType.SQLSERVER.getDriverClass());
    }

    @Override
    protected boolean filterSchema(String schemaName) {
        if (StringUtils.startsWithIgnoreCase(schemaName, "db_")) {
            return false;
        }

        if (ArrayUtils.contains(SYS_DBS, schemaName.toUpperCase())) {
            return false;
        }

        return super.filterSchema(schemaName);
    }

    @Override
    protected boolean filterTable(String tableName) {
        return !StringUtils.equalsIgnoreCase(tableName, "systranschemas");
    }

    @Override
    protected String getSelectTablesSql(String tableName) {
        String sql = "SELECT SCHEMA_NAME(schema_id)          TABLE_SCHEMA\n"
                + "     , a.NAME                TABLE_NAME\n"
                + "     , a.create_date         CREATE_TIME\n"
                + "     , a.modify_date         UPDATE_TIME\n"
                + "     , a.type                TABLE_TYPE\n"
                + "     , null                  AS TABLE_ENGINE\n"
                + "     , null                  AS TABLE_PARTITION\n"
                + "     , SCHEMA_NAME (schema_id) TABLE_SCHEMA,b.rows AS TABLE_ROWS\n"
                + "     , 8 * cast(dpages as bigint) * 1024 DATA_SIZE\n"
                + "     , c.VALUE                           TABLE_COMMENT\n"
                + "FROM sys.TABLES a\n"
                + "         LEFT JOIN sysindexes b ON a.object_id = b.id AND indid IN (0, 1)\n"
                + "         LEFT JOIN sys.extended_properties c\n"
                + "                   ON a.object_id = c.major_id AND c.NAME = 'MS_Description' AND c.minor_id = 0\n"
                + "WHERE SCHEMA_NAME(schema_id) = :schemaName";

        if (StringUtils.isNotBlank(tableName)) {
            sql += " AND a.NAME = :tableName";
        }
        return sql;
    }

    @Override
    protected String getSelectColumnsSql() {
        return "SELECT tbl.name AS table_name,\n"
                + "       col.name AS COLUMN_NAME,\n"
                + "       ext.value AS COLUMN_COMMENT,\n"
                + "       col.column_id AS column_id,\n"
                + "       types.name AS DATA_TYPE,\n"
                + "       col.max_length AS DATA_LENGTH,\n"
                + "       col.precision AS NUMERIC_PRECISION,\n"
                + "       col.scale AS NUMERIC_SCALE,\n"
                + "       col.is_nullable AS IS_NULLABLE,\n"
                + "       def.definition AS DEFAULT_VALUE,\n"
                + "       null AS DATETIME_PRECISION\n"
                + "FROM sys.tables tbl\n"
                + "    INNER JOIN sys.columns col ON tbl.object_id = col.object_id\n"
                + "    LEFT JOIN sys.types types ON col.user_type_id = types.user_type_id\n"
                + "    LEFT JOIN sys.extended_properties ext ON ext.major_id = col.object_id AND ext.minor_id = col.column_id\n"
                + "    LEFT JOIN sys.default_constraints def ON col.default_object_id = def.object_id AND ext.minor_id = col.column_id AND ext.name = 'MS_Description'\n"
                + "WHERE schema_name(tbl.schema_id) = :schemaName\n"
                + "    AND tbl.name = :tableName\n"
                + "ORDER BY tbl.name, col.column_id";
    }

    @Override
    protected String getDDLColumnName(BeeTableType type) {
        return "Item";
    }

    @Override
    protected String createTableSql(BeeTableType type, String schemaName, String tableName) {
        return "EXEC sp_GetDDL1 '" + schemaName + "." + tableName + "'";
    }

    /** https://www.stormrage.com/SQLStuff/sp_GetDDL_Latest.txt */
    @Override
    public String getCreateTableScript(BeeTableType type, String databaseName, String tableName) {
        try (Connection connection = connectionFactory.openConnection();
                Statement statement = connection.createStatement()) {

            String tableSql = createTableSql(type, databaseName, tableName);
            ResultSet dbResult = statement.executeQuery(tableSql);
            if (dbResult.next()) {
                String ddl = dbResult.getString(getDDLColumnName(type));
                ddl = StringUtils.substringAfter(ddl, "GO");
                if (StringUtils.contains(ddl, "GO")) {
                    ddl = StringUtils.substringBefore(ddl, "GO");
                }
                return StringUtils.trim(ddl);
            } else {
                return "DDL is not available";
            }
        } catch (SQLException e) {
            String msg = e.getMessage();
            if (msg.contains("Could not find stored procedure")) {
                msg = "请先创建 Procedure, Procedure 脚本：https://www.stormrage.com/SQLStuff/sp_GetDDL_Latest.txt";
                throw new RuntimeException(msg);
            } else {
                throw new RuntimeException("Get Scheme Error", e);
            }
        }
    }

    @Override
    protected BeeTableType getTableType(String type) {
        if (StringUtils.equalsIgnoreCase(type, "U")) {
            return BeeTableType.TABLE;
        } else {
            throw new IllegalArgumentException("table type is invalid: " + type);
        }
    }
}
